<?php

function dbConnect(){
    return mysqli_connect("localhost", "root", "", "fyptwo");    
}

function dbSelect($table, $what = "*", $where = "", $limit = ""){
    return mysql_query("SELECT ".$what." FROM ".$table.($where == "" ? "":(" WHERE ".$where)).($limit == "" ? "":(" LIMIT ".$limit)));
}

function dbExecuteQuery($conn, $query){
    $mysqli_query = mysqli_query($conn, $query) or die(mysqli_error());
    return $mysqli_query;
}

function resolveOrders($conn, $stock_id){
        $cdt = getCurrentDateTime();
        
        $squery = "SELECT * FROM `order` WHERE stock_id=$stock_id AND action='sell' AND remaining_quantity > 0 ORDER BY bid_value ASC, date_time ASC";
        $sresult = dbExecuteQuery($conn, $squery);
        $sell_orders = array();
        while($row = mysqli_fetch_assoc($sresult))
        {
            $sell_orders[] = $row;
        }
        
        $bquery = "SELECT * FROM `order` WHERE stock_id=$stock_id AND action='buy' AND remaining_quantity > 0 ORDER BY bid_value DESC, date_time ASC";
        $bresult = dbExecuteQuery($conn, $bquery);
        $buy_orders = array();
        while($row = mysqli_fetch_assoc($bresult))
        {
            $buy_orders[] = $row;
        }
        //NOTE: format of each item in array: Array ( [id] => 9 [portfolio_id] => 1 [stock_id] => 1 [quantity] => 1000 [bid_value] => 1.60 [date_time] => 2011-12-01 20:20:00 [action] => sell ) 

//        foreach ($sell_orders as $i){
//            print_r($i);
//            echo "<br />";
//        }
//        echo "<br /><br />";
//        foreach ($buy_orders as $i){
//            print_r($i);
//            echo "<br />";
//        }
//        
//        $query = "SELECT * FROM `order` WHERE stock_id=$stock_id AND quantity > 0 ORDER BY bid_value, date_time";
//        $result = mysqli_query($conn, $query);
//        $buy_orders = array();
//        $sell_orders = array();
//        while($row = mysqli_fetch_array($result))
//        {
//            if ($row['action'] == 'sell'){
//                $sell_orders[] = $row;
//            } else if ($row['action'] == 'buy'){
//                $buy_orders[] = $row;
//            }
//        }
//
        
	/* PS: portfolio_id get from session
	 * 
	 * if ( validate(quantity with their balance) and stock_name is valid){
	 * 		get current date time
	 * 		insert stock_id,bid_price,quantity,portfolio_id,datetime,action into `order` table
	 * 		array_sell = select * from order table where stock_id = user's entry and action = sell and quantity != 0 order by bid_price, date_time ASC (small to big)
	 * 		array_buy = select * from order table where stock_id = user's entry and action = buy and quantity != 0 order by bid_price,date_time DESC (big to small)
	 */
        while ($buy_orders[0]['bid_value'] > $sell_orders[0]['bid_value']){
            $b_order = $buy_orders[0];
            $s_order = $sell_orders[0];
            if ($b_order['remaining_quantity'] > $s_order['remaining_quantity']){
                $deal_qty = $s_order['remaining_quantity'];
            } else if ($b_order['remaining_quantity'] < $s_order['remaining_quantity']){
                $deal_qty = $b_order['remaining_quantity'];
            }
            $deal_price = $s_order['bid_value'];
            
            //deduct remaining_quantity for each order
            $update_order_qty = "UPDATE `order` SET remaining_quantity=remaining_quantity-$deal_qty WHERE id=".$b_order['id']." OR id=".$s_order['id']."";
            dbExecuteQuery($conn, $update_order_qty);
            
            createTransactionRecord($conn, $b_order['id'], $s_order['id'], $deal_price, $deal_qty, $dt);
            
            $total_price = $deal_quantity * $deal_price;
            
            $b_remaining_qty = $b_order['remaining_quantity'] - $deal_qty;
            $b_order_status = updateOrderStatus($b_order['id'], $b_remaining_qty, $b_order['initial_quantity']); //update buyer order status, either pending or success
            if ($b_order_status == 'sucess'){
                updateBuyerPortfolio($conn, $b_order['id'], $stock_id, $deal_qty, $total_price);
                array_shift($b_order);
            }
            
            $s_remaining_qty = $s_order['remaining_quantity'] - $deal_qty;
            $s_order_status = updateOrderStatus($s_order['id'], $s_remaining_qty, $s_order['initial_quantity']); //update seller order status, either pending or success
            if ($s_order_status == 'sucess'){
                //when an order is success, then it will be reflected into the portfolio
                updateSellerPortfolio($conn, $s_order['id'], $stock_id, $deal_qty, $total_price);
                array_shift($s_order);
            }
            
        }
	 /* 		while array_buy[0] > array_sell[0]:
	 * 			if buy_quantity > sell_quantity:
	 * 				deal_quantity = sell_quantity
	 * 			else if buy_quantity < sell_quantity:
	 * 				deal_quantity = buy_quantity
	 *			deal_price = sell_price
	 * 			update order set quantity=quantity-deal_quantity where order_id = buy_order_id or order_id = sell_order_id
	 * 			insert into transaction table values (buy_order_id, sell_order_id, deal_price, current datetime)
	 * 			
         * update order status first: if status is success then update portfolio, else just uipdate the order table which has been above
	 * 			if (buyer order id has the stock before hand): increase quantity (in portfolio table, it will display the current value)
	 * 				else: insert stock info into buyer portfolio
	 * 			if (quantity of seller_order_id == 0): delete stock info from seller portfolio; array_shift(array_sell);
	 * 			if (quantity of bouyer_order_id == 0): array_shift(array_sell);
	 * 			
	 * 			decrease balance of buyer portfolio, increase balance of seller portfolio (deal_quantity * deal_price)

	 * 
	 * 			//update the curent_stock_info table (to display to end user)
	 * 			select * from current_stock_info where stock_id = 1
	 * 			latest_done_price; last_done_price; day_lowest_price; day_highest_price;
	 * 			best_buy_price; best_sell_price; price_change; precent_price_change
	 * 			last_done_price = latest_done_price
	 * 			latest_done_price = deal_price;
	 * 			day_lowest_done_price = if latest_done_price < day_lowest_done_price;
	 * 			day_highest_done_price = if latest_done_price > day_highest_done_price;
	 * 			price_change = latest_done_price - last_done_price;
	 * 			percent_price_change = (price_change/last_done_price) * 100
         * 			update `total_buy_volume` and `total_sell_volume` in `stock` (increase it)
	 * 			update current_stock_info table
	 * }
	 */

}
//
function getCurrentDateTime(){
    date_default_timezone_set('Asia/Singapore');
    return date('Y-m-d H:i:s');    
}

function createOrder($conn, $portfolio_id, $stock_id, $quantity, $bid_price, $date_time, $action){
    $query = "INSERT INTO `order` (`portfolio_id`, `stock_id`, `initial_quantity`, `remaining_quantity`, `bid_value`, `date_time`, `action`, `status`) ";
    $query .= "VALUES ($portfolio_id, $stock_id, $quantity, $quantity, $bid_price, '$date_time', '$action', 'pending')";
    dbExecuteQuery($conn, $query);
}


function getStockID($conn, $stock_name){
    $query = "SELECT id FROM `stock` WHERE company_name='$stock_name'";
    $result = dbExecuteQuery($conn, $query);
    $r = mysqli_fetch_assoc($result);
    return $r['id'];
}

function validateBuyOrder($conn, $quantity, $bid_price, $portfolio_id){
    $total_price = $quantity * $bid_price;
    $query = "SELECT balance FROM `portfolio` WHERE id=$portfolio_id";
    $result = dbExecuteQuery($conn, $query);
    $balance_remaining = mysqli_fetch_assoc($result);
    if ($total_price <= $balance_remaining['balance']){
        return True;
    } else {
        return False;
    }
}

function validateSellOrder($conn, $quantity, $stock_id, $portfolio_id){
    //do they have the stock? do they have enough quantity?
    $query = "SELECT sip.quantity FROM `portfolio` AS p, `stocks_in_portfolio` AS sip, `stock` AS s WHERE p.id=sip.portfolio_id AND sip.stock_id=s.id AND s.id=$stock_id";
    $result = dbExecuteQuery($conn, $query);
    $avail_quantity = mysqli_fetch_assoc($result);
    if (mysqli_num_rows($result) == 1){
        if ($avail_quantity['quantity'] > $quantity){
            return True;
        } else {
            return False;
        }        
    } else {
        return False;
    }

}

function cancelOrder($order_id){
    //delete order from order table, and can only cancel when the initial quantity and remaining quantity is the same
}



function createTransactionRecord($conn, $buyer_o_id, $seller_o_id, $deal_price, $deal_quantity, $cdt){
    $query = "INSERT INTO `transaction`(buyer_order_id, seller_order_id, deal_quantity, done_value, date_time) ";
    $query .= "VALUES ($buyer_o_id, $seller_o_id, $deal_price, $deal_quantity, $cdt)";
    dbExecuteQuery($conn, $query);
}
function updateBuyerPortfolio($conn, $b_pf_id, $stk_id, $deal_quantity, $income){
    $query = "SELECT * FROM `stocks_in_portfolio` WHERE portfolio_id=$b_pf_id AND stock_id=$stk_id";
    $result = dbExecuteQuery($conn, $query);
    if (mysqli_num_rows($result) == 1){
        //increase quantity
        $query = "UPDATE `stocks_in_portfolio` SET quantity=quantity+$deal_quantity WHERE portfolio_id=$b_pf_id AND stock_id=$stk_id";
    } else {
        //add new stocks_in_portfolio record
        $query = "INSERT INTO `stocks_in_portfolio` VALUES ($b_pf_id, $stk_id, $deal_quantity)";
    }
    dbExecuteQuery($conn, $query);
    //decrease balance of buyer portfolio, increase balance of seller portfolio (deal_quantity * deal_price)
    $query = "UPDATE `portfolio` SET balance=balance+$total_price WHERE id=$b_pf_id";
    dbExecuteQuery($conn, $query);
}

function updateOrderStatus($order_id, $remaining_qty, $initial_quantity){
    //if left 0, then success, else set as pending, need to return it for shifting the array
    if ($remaining_qty == 0){
        $query = "UPDATE `order` SET status='success' WHERE order_id=$order_id";
        dbExecuteQuery($conn, $query);
        return "success";
    } else if ($remaining_qty == $initial_quantity){
        $query = "UPDATE `order` SET status='pending' WHERE order_id=$order_id";
        dbExecuteQuery($conn, $query);
        return "pending";
    } else if (($remaining_qty < $initial_quantity) && ($remaining_qty > 0)){
        $query = "UPDATE `order` SET status='in-progress' WHERE order_id=$order_id";
        dbExecuteQuery($conn, $query);
        return "in-progress";
    }
}

function updateSellerPortfolio($conn, $s_pf_id, $stk_id, $deal_quantity, $deal_price){

    $query = "SELECT * FROM `stocks_in_portfolio` WHERE portfolio_id=$b_pf_id AND stock_id=$stk_id";
    $query_result = dbExecuteQuery($conn, $query);
    $result = mysqli_fetch_assoc($query_result);
    $stock_qty = $result['quantity'];
        
    if ($stock_qty > $deal_quantity){
        //decrease quantity by deal_quantity
        $query = "UPDATE `stocks_in_portfolio` SET quantity=quantity-$deal_quantity WHERE portfolio_id=$s_pf_id AND stock_id=$stk_id";
    } else if ($stock_qty == $deal_quantity){
        //delete from portfolio
        $query = "DELETE FROM `stocks_in_portfolio` WHERE portfolio_id=$s_pf_id AND stock_id=$stk_id";
    }
    dbExecuteQuery($conn, $query);
    
    $query = "UPDATE `portfolio` SET seed_balance=seed_balance-$total_price WHERE id=$b_pf_id";
    dbExecuteQuery($conn, $query);
}

function updateCurrentStockInfo($conn, $stock_id, $deal_price, $deal_quantity, $best_buy_price, $best_sell_price){
    /*update the curent_stock_info table (to display to end user)
	 * 			select * from current_stock_info where stock_id = 1
	 * 			latest_done_price; last_done_price; day_lowest_price; day_highest_price;
	 * 			best_buy_price; best_sell_price; price_change; precent_price_change
	 * 			last_done_price = latest_done_price
	 * 			latest_done_price = deal_price;
	 * 			day_lowest_done_price = if latest_done_price < day_lowest_done_price;
	 * 			day_highest_done_price = if latest_done_price > day_highest_done_price;
	 * 			price_change = latest_done_price - last_done_price;
	 * 			percent_price_change = (price_change/last_done_price) * 100
         * 			update `total_buy_volume` and `total_sell_volume` in `stock` (increase it)
	 * 			update current_stock_info table*/
    $query = "SELECT * FROM `current_stock_info` WHERE stock_id=$stock_id";
    $result = dbExecuteQuery($conn, $query);
    $curr_stock_info = mysqli_fetch_assoc($result);
    $last_done_price = $curr_stock_info['latest_done_price'];
    $latest_done_price = $deal_price;
    //$variable = (statement) ? "return if true" : "return if false";
    $day_lowest_done_price = ($latest_done_price < $curr_stock_info['day_lowest_done_price']) ? $latest_done_price : $curr_stock_info['day_lowest_done_price'];
    $day_highest_done_price = ($latest_done_price > $curr_stock_info['day_highest_done_price']) ? $latest_done_price : $curr_stock_info['day_highest_done_price'];

    $price_change = $latest_done_price - $last_done_price;
    $percent_change = ($price_change / $last_done_price) * 100;
    $update_query = "UPDATE `current_stock_info` SET latest_done_price=$latest_done_price, last_done_price=$last_done_price, ";
    $update_query .= "day_lowest_value=$day_lowest_done_price, day_highest_value=$day_highest_done_price, ";
    $update_query .= "best_buy_value=$best_buy_price, best_sell_value=$best_sell_price";
    //best_buy_value, best_sell_value, should I put it into a function?
    
    $vol_query = "UPDATE `stock` SET volume=volume+$deal_quantity";
    dbExecuteQuery($conn, $vol_query);
    //
}
//buy
//

    //success login
    //failed login
    //sadasd
    //hash password
    //work on file naming standard
    //add same data
    //user table (2 or 3 users)
    //function createConn
    //select appropriate data
    //accept orders --> store in db --> resolve --> update db
    //no data out 1 bid (buy n sell)
    //sell > buy = order still there
    //sell = buy = is it disappear?
    //quantiy
    //2 sell orders with same bid price and 1 buy order for certain quantity: expected result should be sell orders with remaining quantity
    //more realistic: cancel orders so need portfolio
    
    
?>
